package com.app.community.mapper;

import com.app.community.dto.CommentDTO;
import com.app.community.dto.LeaderBoardDTO;
import com.app.community.dto.PosterDTO;
import com.app.community.model.entity.Comment;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * 评论的数据库交互
 *  2020/10/27 19:35
 **/
@Mapper
public interface CommentMapper {

    /**
     * 查询评论且查询用户信息,同时避免笛卡尔积
     */
    @Select({"select comment,avatar,nick_name,comment.user_id,date from comment,user where question_id = #{questionId} and user.user_id = comment.user_id order by date desc"})
    List<CommentDTO> selectComment(@Param("questionId") Long questionId);

    /**
     * 发布评论
     */
    @Insert({"insert into comment(user_id,comment,date,question_id) values (#{userId},#{comment},#{date},#{questionId})"})
    void publishComment(@Param("userId") String userId, @Param("comment") String comment, @Param("date") String date, @Param("questionId") Long questionId);

    /**
     * 查询评论数量
     */
    @Select({"select count(*) as commentNum from comment where question_id = #{questionId}"})
    Integer selectCommentNum(@Param("questionId") Long questionId);

    /**
     * 统计不同用户一周内评论数量
     */
    @Results({
            @Result(column = "num", property = "commentNum"),
            @Result(column = "nick_name",property = "nickName")
    })
    @Select({"select count(*) num, question.creator userId, nick_name from comment,question,user where comment.question_id = question.question_id and question.creator = user.user_id and comment.date > date_sub(now(), interval 1 week) group by question.creator;"})
    List<LeaderBoardDTO> selectOneWeekUserLeaderBoard();

    /**
     * 统计一周最火问题(含有封面的问题)
     */
    @Results({
            @Result(column = "num", property = "commentNum"),
            @Result(column = "question_id", property = "questionId"),
            @Result(column = "cover_pic",property = "coverPic")
    })
    @Select({"select count(*) num,cover_pic,question_id,title from question where date > date_sub(now(), interval 1 week) and  cover_pic is not null group by question_id"})
    List<PosterDTO> selectOneWeekPoster();
}
